"""
Case Type   : DISTINCT
Case Name   : 导入数据t_Ustore_DQL_Distinct_customer
Create At   : 2022-02-26
Owner       : wang_zhengyuan
Description :
    1、设置参数enable_copy_server_files参数值为on
    2、gsql连接数据库，创建表
    3、使用copy命令插入数据
    4、创建索引
    5、环境清理
Expect      :
    1、参数值修改成功
    2、gsql连接数据库，创建表成功
    3、数据插入成功
    4、索引创建成功
    5、清理成功
History     :
"""

import os
import unittest
import subprocess

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import macro
from yat.test import Node


class Opengauss_Function_Ustore_DQL_Distinct_Case0003(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----- {os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_root = CommonSH('PrimaryRoot')
        self.com = Common()
        self.constant = Constant()
        self.t_name1 = 't_Ustore_DQL_Distinct_customer'
        self.i_name_list = []
        for i in range(0, 5):
            self.i_name_list.append(f"i_Ustore_DQL_Distinct_customer_{i}")
        self.copy_file1 = 'customer0_0'
        self.cur_path_1 = os.path.join(macro.SCRIPTS_PATH, self.copy_file1)
        self.target_path = macro.DB_INSTANCE_PATH
        self.tar_path_1 = os.path.join(macro.DB_INSTANCE_PATH, self.copy_file1)

    def test_main(self):
        step_txt = '----step1:修改参数值，expect: 参数值修改成功----'
        self.log.info(step_txt)
        self.log.info('---------------获取并打印参数初始值------------------')
        self.show_copy_pram = f'show enable_copy_server_files;'
        self.log.info(self.show_copy_pram)
        cmd_result = self.pri_sh.execut_db_sql(self.show_copy_pram)
        self.log.info(cmd_result)
        self.pram_init = cmd_result.strip().splitlines()[-2].strip()

        self.log.info('---------------开始修改参数值------------------')
        self.pri_sh.execute_gsguc("reload", self.constant.GSGUC_SUCCESS_MSG,
                                  "enable_copy_server_files=on")

        self.log.info('---------------检查参数是否修改成功------------------')
        self.log.info(self.show_copy_pram)
        cmd_result = self.pri_sh.execut_db_sql(self.show_copy_pram)
        self.log.info(cmd_result)
        self.cmd_result_value1 = cmd_result.strip().splitlines()[-2].strip()
        self.assertEqual('on', self.cmd_result_value1, "参数修改失败")

        step_txt = '----step2:创建表，expect: 创建成功----'
        self.log.info(step_txt)
        create_sql = f"drop table if exists {self.t_name1};\
            CREATE TABLE {self.t_name1} ( \
                                    c_id int , \
                                    c_d_id int , \
                                    c_w_id int , \
                                    c_first varchar(16) , \
                                    c_middle char(2) , \
                                    c_last varchar(16) , \
                                    c_street_1 varchar(20) , \
                                    c_street_2 varchar(20) , \
                                    c_city varchar(20) , \
                                    c_state char(2) , \
                                    c_zip char(9) , \
                                    c_phone char(16) , \
                                    c_since timestamp , \
                                    c_credit char(2) , \
                                    c_credit_lim numeric(12,2) , \
                                    c_discount numeric(4,4) , \
                                    c_balance numeric(12,2) , \
                                    c_ytd_payment numeric(12,2) , \
                                    c_payment_cnt int , \
                                    c_delivery_cnt int , \
                                    c_data varchar(500))\
            WITH (STORAGE_TYPE=USTORE) partition by range (c_id)\
            (\
            partition vector_engine_Customer_1 values less than (10),\
            partition vector_engine_Customer_2 values less than (77),\
            partition vector_engine_Customer_3 values less than (337),\
            partition vector_engine_Customer_4 values less than (573),\
            partition vector_engine_Customer_5 values less than (1357),\
            partition vector_engine_Customer_6 values less than (2033),\
            partition vector_engine_Customer_7 values less than (2087),\
            partition vector_engine_Customer_8 values less than (2387),\
            partition vector_engine_Customer_9 values less than (2687),\
            partition vector_engine_Customer_10 values less than (2987),\
            partition vector_engine_Customer_11 values less than (maxvalue)\
            );"
        self.log.info(create_sql)
        create_result = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(create_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_result,
                      '执行失败:' + step_txt)

        step_txt = '----step3:使用copy命令插入数据，expect: 数据插入成功----'
        self.log.info(step_txt)
        text = '----step3.1: 获取当前主机的IP地址 expect: 成功----'
        self.log.info(text)
        cmd = "ifconfig -a|grep inet6 -a2|grep broadcast|awk '{print $2}'"
        self.log.info(cmd)
        self.cur_ip = subprocess.getoutput(cmd).strip()
        self.log.info(self.cur_ip)

        text = '----step3.2: 复制文件到数据库主机对应目录 expect: 成功----'
        self.log.info(text)
        if self.cur_ip == self.pri_sh.node.ssh_host:
            self.log.info('当前主机是数据库主机，无需远程复制')
            cmd = f"cp {self.cur_path_1} {self.target_path};"
            self.log.info(cmd)
            res = self.pri_sh.node.sh(cmd).result()
            self.log.info(res)
        else:
            self.com.scp_file(self.pri_sh.node, self.copy_file1,
                              self.target_path)

        text = '----step3.3: 查看数据库主机目标路径下文件 expect: 成功----'
        self.log.info(text)
        cmd = f"ls -l {self.target_path}| grep -E '{self.copy_file1}'"
        self.log.info(cmd)
        res = self.pri_sh.node.sh(cmd).result()
        self.log.info(res)
        self.assertNotIn('No such file or directory', res, '文件拷贝失败')
        self.assertIn(f'{self.copy_file1}', res, '文件拷贝失败')

        text = '----step3.4:使用copy命令插入数据，expect: 数据插入成功----'
        self.log.info(text)
        self.log.info('---------------使用copy命令插入数据------------------')
        copy_sql = f"copy {self.t_name1} from '{self.tar_path_1}' " \
            f"delimiters ',';"
        self.log.info(copy_sql)
        copy_result = self.pri_sh.execut_db_sql(copy_sql)
        self.log.info(copy_result)
        self.assertEqual(copy_result.strip().splitlines()[0].strip(),
                         'COPY 300000', '执行失败:' + step_txt)

        step_txt = '----step4: 创建索引,expect: 索引创建成功----'
        self.log.info(step_txt)
        column_list = ['c_w_id, c_d_id, c_id', 'c_city', 'c_credit_lim',
                       'c_data', 'c_w_id, c_d_id, c_last, c_first']
        for a in range(0, 5):
            create_sql = f"drop index if exists {self.i_name_list[a]} " \
                f"CASCADE;create index {self.i_name_list[a]} " \
                f"on {self.t_name1}({column_list[a]}) local;"
            self.log.info(create_sql)
            create_result = self.pri_sh.execut_db_sql(create_sql)
            self.log.info(create_result)
            self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG,
                          create_result,
                          f'创建{self.i_name_list[a]}失败：' + step_txt)

    def tearDown(self):
        self.log.info('----this is teardown----')
        step_txt = '----step5: 环境清理，expect:清理成功----'
        self.log.info(step_txt)
        drop_sql = f'drop table if exists {self.t_name1};'
        self.log.info(drop_sql)
        self.log.info('由于后续用例依赖暂不删表')

        rm_cmd = f"rm -rf {self.tar_path_1} "
        rm_res = self.pri_sh.node.sh(rm_cmd).result()
        self.log.info(rm_res)

        self.log.info('---------------开始还原参数值------------------')
        self.pri_sh.execute_gsguc("reload", self.constant.GSGUC_SUCCESS_MSG,
                                  f"enable_copy_server_files={self.pram_init}")

        self.log.info('---------------检查参数是否还原成功------------------')
        self.log.info(self.show_copy_pram)
        cmd_result = self.pri_sh.execut_db_sql(self.show_copy_pram)
        self.log.info(cmd_result)
        self.cmd_result_value2 = cmd_result.strip().splitlines()[-2].strip()
        self.assertEqual(rm_res, '', '文件删除失败' + step_txt)
        self.assertEqual(self.pram_init, self.cmd_result_value2, "参数还原失败")
        self.log.info(f'----- {os.path.basename(__file__)} end-----')
